import pymysql
import json
import random
import time
from datetime import datetime, timedelta

# 城市列表（含模拟的城市代码）
CITY_DATA = [
    {"name": "北京", "code": 110000, "province": "北京"},
    {"name": "上海", "code": 310000, "province": "上海"},
    {"name": "广州", "code": 440100, "province": "广东"},
    {"name": "深圳", "code": 440300, "province": "广东"},
    {"name": "成都", "code": 510100, "province": "四川"},
    {"name": "重庆", "code": 500000, "province": "重庆"},
    {"name": "杭州", "code": 330100, "province": "浙江"},
    {"name": "武汉", "code": 420100, "province": "湖北"},
    {"name": "西安", "code": 610100, "province": "陕西"},
    {"name": "天津", "code": 120000, "province": "天津"},
    {"name": "苏州", "code": 320500, "province": "江苏"},
    {"name": "南京", "code": 320100, "province": "江苏"},
    {"name": "郑州", "code": 410100, "province": "河南"},
    {"name": "长沙", "code": 430100, "province": "湖南"},
    {"name": "东莞", "code": 441900, "province": "广东"},
    {"name": "沈阳", "code": 210100, "province": "辽宁"},
    {"name": "青岛", "code": 370200, "province": "山东"},
    {"name": "合肥", "code": 340100, "province": "安徽"},
    {"name": "佛山", "code": 440600, "province": "广东"},
    {"name": "宁波", "code": 330200, "province": "浙江"},
    {"name": "昆明", "code": 530100, "province": "云南"},
    {"name": "福州", "code": 350100, "province": "福建"},
    {"name": "无锡", "code": 320200, "province": "江苏"},
    {"name": "厦门", "code": 350200, "province": "福建"},
    {"name": "济南", "code": 370100, "province": "山东"},
    {"name": "大连", "code": 210200, "province": "辽宁"},
    {"name": "哈尔滨", "code": 230100, "province": "黑龙江"},
    {"name": "温州", "code": 330300, "province": "浙江"},
    {"name": "石家庄", "code": 130100, "province": "河北"},
    {"name": "泉州", "code": 350500, "province": "福建"},
    {"name": "南宁", "code": 450100, "province": "广西"},
    {"name": "长春", "code": 220100, "province": "吉林"},
    {"name": "南昌", "code": 360100, "province": "江西"},
    {"name": "贵阳", "code": 520100, "province": "贵州"},
    {"name": "金华", "code": 330700, "province": "浙江"},
    {"name": "常州", "code": 320400, "province": "江苏"},
    {"name": "惠州", "code": 441300, "province": "广东"},
    {"name": "嘉兴", "code": 330400, "province": "浙江"},
    {"name": "南通", "code": 320600, "province": "江苏"},
    {"name": "徐州", "code": 320300, "province": "江苏"},
    {"name": "太原", "code": 140100, "province": "山西"},
    {"name": "珠海", "code": 440400, "province": "广东"},
    {"name": "中山", "code": 442000, "province": "广东"},
    {"name": "保定", "code": 130600, "province": "河北"},
    {"name": "兰州", "code": 620100, "province": "甘肃"},
    {"name": "台州", "code": 331000, "province": "浙江"},
    {"name": "绍兴", "code": 330600, "province": "浙江"},
    {"name": "烟台", "code": 370600, "province": "山东"},
    {"name": "廊坊", "code": 131000, "province": "河北"},
    {"name": "潍坊", "code": 370700, "province": "山东"},
    {"name": "扬州", "code": 321000, "province": "江苏"},
    {"name": "海口", "code": 460100, "province": "海南"},
    {"name": "汕头", "code": 440500, "province": "广东"},
    {"name": "洛阳", "code": 410300, "province": "河南"},
    {"name": "乌鲁木齐", "code": 650100, "province": "新疆"},
    {"name": "临沂", "code": 371300, "province": "山东"},
    {"name": "唐山", "code": 130200, "province": "河北"},
    {"name": "镇江", "code": 321100, "province": "江苏"},
    {"name": "盐城", "code": 320900, "province": "江苏"},
    {"name": "湖州", "code": 330500, "province": "浙江"},
    {"name": "赣州", "code": 360700, "province": "江西"},
    {"name": "泰州", "code": 321200, "province": "江苏"},
    {"name": "济宁", "code": 370800, "province": "山东"},
    {"name": "呼和浩特", "code": 150100, "province": "内蒙古"},
    {"name": "咸阳", "code": 610400, "province": "陕西"},
    {"name": "漳州", "code": 350600, "province": "福建"},
    {"name": "揭阳", "code": 445200, "province": "广东"},
    {"name": "江门", "code": 440700, "province": "广东"},
    {"name": "桂林", "code": 450300, "province": "广西"},
    {"name": "邯郸", "code": 130400, "province": "河北"},
    {"name": "芜湖", "code": 340200, "province": "安徽"},
    {"name": "三亚", "code": 460200, "province": "海南"},
    {"name": "阜阳", "code": 341200, "province": "安徽"},
    {"name": "淮安", "code": 320800, "province": "江苏"},
    {"name": "遵义", "code": 520300, "province": "贵州"},
    {"name": "银川", "code": 640100, "province": "宁夏"},
    {"name": "衡阳", "code": 430400, "province": "湖南"},
    {"name": "上饶", "code": 361100, "province": "江西"},
    {"name": "柳州", "code": 450200, "province": "广西"},
    {"name": "淄博", "code": 370300, "province": "山东"},
    {"name": "莆田", "code": 350300, "province": "福建"},
    {"name": "绵阳", "code": 510700, "province": "四川"},
    {"name": "湛江", "code": 440800, "province": "广东"},
    {"name": "商丘", "code": 411400, "province": "河南"},
    {"name": "宜昌", "code": 420500, "province": "湖北"},
    {"name": "沧州", "code": 130900, "province": "河北"},
    {"name": "连云港", "code": 320700, "province": "江苏"},
    {"name": "南阳", "code": 411300, "province": "河南"},
    {"name": "九江", "code": 360400, "province": "江西"},
    {"name": "新乡", "code": 410700, "province": "河南"},
    {"name": "信阳", "code": 411500, "province": "河南"},
    {"name": "襄阳", "code": 420600, "province": "湖北"},
    {"name": "岳阳", "code": 430600, "province": "湖南"},
    {"name": "蚌埠", "code": 340300, "province": "安徽"},
    {"name": "驻马店", "code": 411700, "province": "河南"},
    {"name": "滁州", "code": 341100, "province": "安徽"},
    {"name": "威海", "code": 371000, "province": "山东"},
    {"name": "宿迁", "code": 321300, "province": "江苏"},
    {"name": "株洲", "code": 430200, "province": "湖南"},
    {"name": "宁德", "code": 350900, "province": "福建"},
    {"name": "邢台", "code": 130500, "province": "河北"},
    {"name": "潮州", "code": 445100, "province": "广东"},
    {"name": "秦皇岛", "code": 130300, "province": "河北"},
    {"name": "肇庆", "code": 441200, "province": "广东"},
    {"name": "荆州", "code": 421000, "province": "湖北"},
    {"name": "周口", "code": 411600, "province": "河南"},
    {"name": "马鞍山", "code": 340500, "province": "安徽"},
    {"name": "清远", "code": 441800, "province": "广东"},
    {"name": "宿州", "code": 341300, "province": "安徽"},
    {"name": "鞍山", "code": 210300, "province": "辽宁"},
    {"name": "安庆", "code": 340800, "province": "安徽"},
    {"name": "菏泽", "code": 371700, "province": "山东"},
    {"name": "宜春", "code": 360900, "province": "江西"},
    {"name": "黄冈", "code": 421100, "province": "湖北"},
    {"name": "泰安", "code": 370900, "province": "山东"},
    {"name": "南充", "code": 511300, "province": "四川"},
    {"name": "六安", "code": 341500, "province": "安徽"},
    {"name": "大庆", "code": 230600, "province": "黑龙江"},
    {"name": "舟山", "code": 330900, "province": "浙江"},
    {"name": "常德", "code": 430700, "province": "湖南"},
    {"name": "渭南", "code": 610500, "province": "陕西"},
    {"name": "孝感", "code": 420900, "province": "湖北"},
    {"name": "丽水", "code": 331100, "province": "浙江"},
    {"name": "运城", "code": 140800, "province": "山西"},
    {"name": "德州", "code": 371400, "province": "山东"},
    {"name": "许昌", "code": 411000, "province": "河南"},
    {"name": "湘潭", "code": 430300, "province": "湖南"},
    {"name": "晋中", "code": 140700, "province": "山西"},
    {"name": "安阳", "code": 410500, "province": "河南"},
    {"name": "三明", "code": 350400, "province": "福建"},
    {"name": "开封", "code": 410200, "province": "河南"},
    {"name": "郴州", "code": 431000, "province": "湖南"},
    {"name": "茂名", "code": 440900, "province": "广东"},
    {"name": "邵阳", "code": 430500, "province": "湖南"},
    {"name": "德阳", "code": 510600, "province": "四川"},
    {"name": "龙岩", "code": 350800, "province": "福建"},
    {"name": "南平", "code": 350700, "province": "福建"},
    {"name": "淮南", "code": 340400, "province": "安徽"},
    {"name": "黄石", "code": 420200, "province": "湖北"},
    {"name": "营口", "code": 210800, "province": "辽宁"},
    {"name": "亳州", "code": 341600, "province": "安徽"},
    {"name": "日照", "code": 371100, "province": "山东"},
    {"name": "西宁", "code": 630100, "province": "青海"},
    {"name": "衢州", "code": 330800, "province": "浙江"},
    {"name": "东营", "code": 370500, "province": "山东"},
    {"name": "吉林", "code": 220200, "province": "吉林"},
    {"name": "韶关", "code": 440200, "province": "广东"},
    {"name": "枣庄", "code": 370400, "province": "山东"},
    {"name": "包头", "code": 150200, "province": "内蒙古"},
    {"name": "怀化", "code": 431200, "province": "湖南"},
    {"name": "宣城", "code": 341800, "province": "安徽"},
    {"name": "临汾", "code": 141000, "province": "山西"},
    {"name": "聊城", "code": 371500, "province": "山东"},
    {"name": "梅州", "code": 441400, "province": "广东"},
    {"name": "盘锦", "code": 211100, "province": "辽宁"},
    {"name": "锦州", "code": 210700, "province": "辽宁"},
    {"name": "榆林", "code": 610800, "province": "陕西"},
    {"name": "北海", "code": 450500, "province": "广西"},
    {"name": "宝鸡", "code": 610300, "province": "陕西"},
    {"name": "抚州", "code": 361000, "province": "江西"},
    {"name": "景德镇", "code": 360200, "province": "江西"},
    {"name": "玉林", "code": 450900, "province": "广西"},
    {"name": "十堰", "code": 420300, "province": "湖北"},
    {"name": "汕尾", "code": 441500, "province": "广东"},
    {"name": "咸宁", "code": 421200, "province": "湖北"},
    {"name": "宜宾", "code": 511500, "province": "四川"},
    {"name": "焦作", "code": 410800, "province": "河南"},
    {"name": "平顶山", "code": 410400, "province": "河南"},
    {"name": "滨州", "code": 371600, "province": "山东"},
    {"name": "吉安", "code": 360800, "province": "江西"},
    {"name": "永州", "code": 431100, "province": "湖南"},
    {"name": "益阳", "code": 430900, "province": "湖南"},
    {"name": "黔南", "code": 522700, "province": "贵州"},
    {"name": "丹东", "code": 210600, "province": "辽宁"},
    {"name": "曲靖", "code": 530300, "province": "云南"},
    {"name": "乐山", "code": 511100, "province": "四川"},
    {"name": "黔东南", "code": 522600, "province": "贵州"},
    {"name": "张家口", "code": 130700, "province": "河北"},
    {"name": "黄山", "code": 341000, "province": "安徽"},
    {"name": "鄂尔多斯", "code": 150600, "province": "内蒙古"},
    {"name": "阳江", "code": 441700, "province": "广东"},
    {"name": "泸州", "code": 510500, "province": "四川"},
    {"name": "恩施", "code": 422800, "province": "湖北"},
    {"name": "衡水", "code": 131100, "province": "河北"},
    {"name": "铜陵", "code": 340700, "province": "安徽"},
    {"name": "承德", "code": 130800, "province": "河北"},
    {"name": "红河", "code": 532500, "province": "云南"},
    {"name": "大理", "code": 532900, "province": "云南"},
    {"name": "大同", "code": 140200, "province": "山西"},
    {"name": "漯河", "code": 411100, "province": "河南"},
    {"name": "葫芦岛", "code": 211400, "province": "辽宁"},
    {"name": "河源", "code": 441600, "province": "广东"},
    {"name": "娄底", "code": 431300, "province": "湖南"},
    {"name": "延边", "code": 222400, "province": "吉林"},
    {"name": "齐齐哈尔", "code": 230200, "province": "黑龙江"},
    {"name": "延安", "code": 610600, "province": "陕西"},
    {"name": "抚顺", "code": 210400, "province": "辽宁"},
    {"name": "拉萨", "code": 540100, "province": "西藏"},
    {"name": "铜仁", "code": 520600, "province": "贵州"},
    {"name": "长治", "code": 140400, "province": "山西"},
    {"name": "达州", "code": 511700, "province": "四川"},
    {"name": "鄂州", "code": 420700, "province": "湖北"},
    {"name": "忻州", "code": 140900, "province": "山西"},
    {"name": "吕梁", "code": 141100, "province": "山西"},
    {"name": "淮北", "code": 340600, "province": "安徽"},
    {"name": "濮阳", "code": 410900, "province": "河南"},
    {"name": "眉山", "code": 511400, "province": "四川"},
    {"name": "池州", "code": 341700, "province": "安徽"},
    {"name": "荆门", "code": 420800, "province": "湖北"},
    {"name": "汉中", "code": 610700, "province": "陕西"},
    {"name": "辽阳", "code": 211000, "province": "辽宁"},
    {"name": "梧州", "code": 450400, "province": "广西"},
    {"name": "鹰潭", "code": 360600, "province": "江西"},
    {"name": "百色", "code": 451000, "province": "广西"},
    {"name": "毕节", "code": 520500, "province": "贵州"},
    {"name": "钦州", "code": 450700, "province": "广西"},
    {"name": "云浮", "code": 445300, "province": "广东"},
    {"name": "佳木斯", "code": 230800, "province": "黑龙江"},
    {"name": "朝阳", "code": 211300, "province": "辽宁"},
    {"name": "贵港", "code": 450800, "province": "广西"},
    {"name": "丽江", "code": 530700, "province": "云南"},
    {"name": "四平", "code": 220300, "province": "吉林"},
    {"name": "内江", "code": 511000, "province": "四川"},
    {"name": "六盘水", "code": 520200, "province": "贵州"},
    {"name": "安顺", "code": 520400, "province": "贵州"},
    {"name": "三门峡", "code": 411200, "province": "河南"},
    {"name": "赤峰", "code": 150400, "province": "内蒙古"},
    {"name": "新余", "code": 360500, "province": "江西"},
    {"name": "牡丹江", "code": 231000, "province": "黑龙江"},
    {"name": "晋城", "code": 140500, "province": "山西"},
    {"name": "自贡", "code": 510300, "province": "四川"},
    {"name": "本溪", "code": 210500, "province": "辽宁"},
    {"name": "防城港", "code": 450600, "province": "广西"},
    {"name": "铁岭", "code": 211200, "province": "辽宁"},
    {"name": "随州", "code": 421300, "province": "湖北"},
    {"name": "广安", "code": 511600, "province": "四川"},
    {"name": "广元", "code": 510800, "province": "四川"},
    {"name": "天水", "code": 620500, "province": "甘肃"},
    {"name": "遂宁", "code": 510900, "province": "四川"},
    {"name": "萍乡", "code": 360300, "province": "江西"},
    {"name": "西双版纳", "code": 532800, "province": "云南"},
    {"name": "绥化", "code": 231200, "province": "黑龙江"},
    {"name": "鹤壁", "code": 410600, "province": "河南"},
    {"name": "湘西", "code": 433100, "province": "湖南"},
    {"name": "松原", "code": 220700, "province": "吉林"},
    {"name": "阜新", "code": 210900, "province": "辽宁"},
    {"name": "酒泉", "code": 620900, "province": "甘肃"},
    {"name": "张家界", "code": 430800, "province": "湖南"},
    {"name": "黔西南", "code": 522300, "province": "贵州"},
    {"name": "保山", "code": 530500, "province": "云南"},
    {"name": "昭通", "code": 530600, "province": "云南"},
    {"name": "克拉玛依", "code": 650200, "province": "新疆"},
    {"name": "呼伦贝尔", "code": 150700, "province": "内蒙古"},
    {"name": "贺州", "code": 451100, "province": "广西"},
    {"name": "通化", "code": 220500, "province": "吉林"},
    {"name": "阳泉", "code": 140300, "province": "山西"},
    {"name": "河池", "code": 451200, "province": "广西"},
    {"name": "来宾", "code": 451300, "province": "广西"},
    {"name": "玉溪", "code": 530400, "province": "云南"},
    {"name": "安康", "code": 610900, "province": "陕西"},
    {"name": "通辽", "code": 150500, "province": "内蒙古"},
    {"name": "德宏", "code": 533100, "province": "云南"},
    {"name": "楚雄", "code": 532300, "province": "云南"},
    {"name": "朔州", "code": 140600, "province": "山西"},
    {"name": "伊犁", "code": 654000, "province": "新疆"},
    {"name": "文山", "code": 532600, "province": "云南"},
    {"name": "嘉峪关", "code": 620200, "province": "甘肃"},
    {"name": "凉山", "code": 513400, "province": "四川"},
    {"name": "资阳", "code": 512000, "province": "四川"},
    {"name": "锡林郭勒", "code": 152500, "province": "内蒙古"},
    {"name": "雅安", "code": 511800, "province": "四川"},
    {"name": "普洱", "code": 530800, "province": "云南"},
    {"name": "崇左", "code": 451400, "province": "广西"},
    {"name": "庆阳", "code": 621000, "province": "甘肃"},
    {"name": "巴音郭楞", "code": 652800, "province": "新疆"},
    {"name": "乌兰察布", "code": 150900, "province": "内蒙古"},
    {"name": "白山", "code": 220600, "province": "吉林"},
    {"name": "昌吉", "code": 652300, "province": "新疆"},
    {"name": "白城", "code": 220800, "province": "吉林"},
    {"name": "兴安", "code": 152200, "province": "内蒙古"},
    {"name": "定西", "code": 621100, "province": "甘肃"},
    {"name": "喀什", "code": 653100, "province": "新疆"},
    {"name": "白银", "code": 620400, "province": "甘肃"},
    {"name": "陇南", "code": 621200, "province": "甘肃"},
    {"name": "张掖", "code": 620700, "province": "甘肃"},
    {"name": "商洛", "code": 611000, "province": "陕西"},
    {"name": "黑河", "code": 231100, "province": "黑龙江"},
    {"name": "哈密", "code": 652200, "province": "新疆"},
    {"name": "吴忠", "code": 640300, "province": "宁夏"},
    {"name": "攀枝花", "code": 510400, "province": "四川"},
    {"name": "巴彦淖尔", "code": 150800, "province": "内蒙古"},
    {"name": "巴中", "code": 511900, "province": "四川"},
    {"name": "鸡西", "code": 230300, "province": "黑龙江"},
    {"name": "乌海", "code": 150300, "province": "内蒙古"},
    {"name": "临沧", "code": 530900, "province": "云南"},
    {"name": "海东", "code": 630200, "province": "青海"},
    {"name": "双鸭山", "code": 230500, "province": "黑龙江"},
    {"name": "阿克苏", "code": 652900, "province": "新疆"},
    {"name": "石嘴山", "code": 640200, "province": "宁夏"},
    {"name": "阿拉善", "code": 152900, "province": "内蒙古"},
    {"name": "海西", "code": 632800, "province": "青海"},
    {"name": "平凉", "code": 620800, "province": "甘肃"},
    {"name": "辽源", "code": 220400, "province": "吉林"},
    {"name": "临夏", "code": 622900, "province": "甘肃"},
    {"name": "铜川", "code": 610200, "province": "陕西"},
    {"name": "金昌", "code": 620300, "province": "甘肃"},
    {"name": "鹤岗", "code": 230400, "province": "黑龙江"},
    {"name": "伊春", "code": 230700, "province": "黑龙江"},
    {"name": "林芝", "code": 540400, "province": "西藏"},
    {"name": "固原", "code": 640400, "province": "宁夏"},
    {"name": "武威", "code": 620600, "province": "甘肃"},
    {"name": "儋州", "code": 460400, "province": "海南"},
    {"name": "吐鲁番", "code": 652100, "province": "新疆"},
    {"name": "甘孜", "code": 513300, "province": "四川"},
    {"name": "中卫", "code": 640500, "province": "宁夏"},
    {"name": "怒江", "code": 533300, "province": "云南"},
    {"name": "和田", "code": 653200, "province": "新疆"},
    {"name": "迪庆", "code": 533400, "province": "云南"},
    {"name": "甘南", "code": 623000, "province": "甘肃"},
    {"name": "阿坝", "code": 513200, "province": "四川"},
    {"name": "大兴安岭", "code": 232700, "province": "黑龙江"},
    {"name": "七台河", "code": 230900, "province": "黑龙江"},
    {"name": "山南", "code": 540500, "province": "西藏"},
    {"name": "日喀则", "code": 540200, "province": "西藏"},
    {"name": "塔城", "code": 654200, "province": "新疆"},
    {"name": "博尔塔拉", "code": 652700, "province": "新疆"},
    {"name": "昌都", "code": 540300, "province": "西藏"},
    {"name": "阿勒泰", "code": 654300, "province": "新疆"},
    {"name": "玉树", "code": 632700, "province": "青海"},
    {"name": "海南", "code": 632500, "province": "青海"},
    {"name": "克孜勒苏", "code": 653000, "province": "新疆"},
    {"name": "阿里", "code": 542500, "province": "西藏"},
    {"name": "海北", "code": 632200, "province": "青海"},
    {"name": "黄南", "code": 632300, "province": "青海"},
    {"name": "果洛", "code": 632600, "province": "青海"},
    {"name": "那曲", "code": 540600, "province": "西藏"},
    {"name": "三沙", "code": 460300, "province": "海南"}
]

# 天气现象列表
WEATHER_CONDITIONS = ["晴", "多云", "阴", "小雨", "中雨", "大雨", "雷阵雨", "小雪", "中雪", "大雪", "雾", "霾"]
# 风向列表
WIND_DIRECTIONS = ["北风", "东北风", "东风", "东南风", "南风", "西南风", "西风", "西北风"]
# 生活指数类型
LIVING_INDEX_TYPES = [
    "穿衣", "洗车", "运动", "紫外线", "感冒", "旅游", "空气污染",
    "舒适度", "防晒", "钓鱼", "交通", "约会", "心情"
]

def generate_weather_data(city):
    """生成模拟的天气数据"""
    # 基础天气数据
    temperature = random.randint(-10, 38)  # 温度范围-10℃~38℃
    humidity = random.randint(20, 95)      # 湿度20%~95%
    weather = random.choice(WEATHER_CONDITIONS)
    wind_direction = random.choice(WIND_DIRECTIONS)
    wind_power = f"{random.randint(1, 12)}级"

    lives = {
        "province": city["province"],
        "city": city["name"],
        "adcode": str(city["code"]),
        "weather": weather,
        "temperature": str(temperature),
        "winddirection": wind_direction,
        "windpower": wind_power,
        "humidity": str(humidity) + "%",
        "reporttime": datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    }


    living_index = []
    for index_type in LIVING_INDEX_TYPES:  # 遍历所有类型
        index_value = random.choice(["适宜", "较适宜", "中等", "较不宜", "不宜"])
        living_index.append({
            "name": f"{index_type}指数",
            "level": random.randint(1, 5),  # 1-5级（1级最高，5级最低）
            "category": index_value,
            "text": f"今日{index_type}指数{index_value}，{random.choice(['注意防护','可正常进行','建议减少外出'])}"
        })

    return lives, living_index

def update_weather_data():
    """更新所有城市的天气数据到数据库"""
    conn = pymysql.connect(
        host='node101',
        port=9030,
        user='root',
        password='123456',
        database='jtp_data_warehouse',
        charset='utf8mb4'
    )

    current_time = datetime.now()
    # 使用整点时间作为报告时间（保证每小时唯一）
    report_time = current_time.replace(minute=0, second=0, microsecond=0)

    try:
        with conn.cursor() as cursor:
            for city in CITY_DATA:
                lives, living_index = generate_weather_data(city)
                code = city["code"]
                report_time = current_time.replace(minute=0, second=0, microsecond=0)

                # 1. 删除旧数据（根据唯一键）
                delete_sql = """
                DELETE FROM spider_amap_weather_data_dtl 
                WHERE code = %s AND report_time = %s
                """
                cursor.execute(delete_sql, (code, report_time))

                # 2. 插入新数据
                insert_sql = """
                INSERT INTO spider_amap_weather_data_dtl (
                    code, report_time, province, city, 
                    lives, living_index, update_time
                ) VALUES (
                    %s, %s, %s, %s, 
                    %s, %s, NOW()
                )
                """
                cursor.execute(insert_sql, (
                    code,
                    report_time,
                    city["province"],
                    city["name"],
                    json.dumps(lives, ensure_ascii=False),
                    json.dumps(living_index, ensure_ascii=False)
                ))

        conn.commit()
        print(f"{current_time} - 成功更新{len(CITY_DATA)}个城市数据")

    except Exception as e:
        conn.rollback()
        print(f"数据库操作失败: {str(e)}")
    finally:
        conn.close()

def main():
    """主循环函数，每小时执行一次"""
    while True:
        try:
            update_weather_data()
        except Exception as e:
            print(f"更新失败: {str(e)}")

        # 计算下一个整点时间
        next_hour = datetime.now() + timedelta(hours=1)
        next_run = next_hour.replace(minute=0, second=0, microsecond=0)
        sleep_seconds = (next_run - datetime.now()).total_seconds()

        print(f"下次执行时间: {next_run}")
        time.sleep(max(0, sleep_seconds))

if __name__ == "__main__":
    main()